﻿/****************************************************************************
*                                                                           *
* Copyright (c) RSC. All rights reserved(2010).								*
* Developer: Marius Ionescu    						                        *
*                                                                           *
****************************************************************************/

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using SmoTable = Microsoft.SqlServer.Management.Smo.Table;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Text;
using System.IO;
using XMLTools;

public partial class ImportSIUI : SessionPageBase
{
    private string gvUniqueID = String.Empty;
    private static string gvSortExpr = String.Empty;
    private static string prefix_Table = String.Empty;
    private string message = String.Empty;
    private string connectionString_XML = String.Empty;
    private string connectionString_NOM = String.Empty;

    private string connectionString
    {
        get { return (string)(this.ViewState["connectionString"] ?? ""); }
        set { this.ViewState["connectionString"] = value; }
    }

    private string GridViewSortDirection
    {
        get { return (string)(this.ViewState["GridViewSortDirection"] ?? "DESC"); }
        set { this.ViewState["GridViewSortDirection"] = value; }
    }

    private string GetSortDirection()
    {
        switch (GridViewSortDirection)
        {
            case "ASC":
                GridViewSortDirection = "DESC";
                break;
            case "DESC":
                GridViewSortDirection = "ASC";
                break;
        }
        return GridViewSortDirection;
    }

    protected override void OnInit(EventArgs e)
    {
        base.OnInit(e);
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            connectionString_XML = "Data Source=DEVH2;Initial Catalog=SIUI_XML;User Id=sa;Password=sa;";
            connectionString_NOM = "Data Source=DEVH2;Initial Catalog=SIUI_Nomenclatoare;User Id=sa;Password=sa;";

            if (string.IsNullOrEmpty(Hipocrate.DB.DBConnection.variableConnectionString))
            {
                connectionString = connectionString_NOM;
                Hipocrate.DB.DBConnection.variableConnectionString = connectionString;
            }

            if (!IsPostBack)
            {
                DoFillCombo();
                connectionString = connectionString_NOM;
                Hipocrate.DB.DBConnection.variableConnectionString = connectionString;
            }

            Load_Grid();
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
        }
    }

    protected void DoFillCombo()
    {
        ddlImportType.Items.Clear();
        var dsImportType = Hipocrate.DB.DBConnection.GetDataVariable("ImportTypes_Select", new string[] { }, new object[] { });
        ddlImportType.DataSource = dsImportType.Tables[0].DefaultView;
        ddlImportType.DataTextField = "name";
        ddlImportType.DataValueField = "id";
        ddlImportType.AppendDataBoundItems = true;
        ddlImportType.DataBind();

        ddl.Items.Clear();
        var ddlItems = Hipocrate.DB.DBConnection.GetDataVariable("ExportItems_Select", new string[] { }, new object[] { });
        Session["ds_export_items"] = ddlItems;
        ddlItems.Tables[0].DefaultView.RowFilter = "TypeID='" + ddlImportType.SelectedValue + "'";
        ddl.DataSource = ddlItems.Tables[0].DefaultView;
        ddl.DataTextField = "Section_code";
        ddl.DataValueField = "id";
        ddl.AppendDataBoundItems = true;
        ddl.DataBind();

        string strRootPath = System.Web.HttpContext.Current.Request.MapPath("~/Tools/TempNomenclator/");
        System.IO.DirectoryInfo path = new System.IO.DirectoryInfo(strRootPath);
        int count = 0;

        foreach (System.IO.FileInfo file in path.GetFiles())
        {
            drpFiles.Items.Insert(count, file.Name);
            count++;
        }
    }

    protected void Load_Grid()
    {
        var ds = Hipocrate.DB.DBConnection.GetDataVariable("History_Select", new string[] { }, new object[] { });
        var dv = ds.Tables[0].DefaultView;
        GridView1.DataSource = dv;
        GridView1.DataBind();
    }

    protected void ddlImportType_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlImportType.SelectedItem.Text == "Nomenclatoare")
            connectionString = connectionString_NOM;
        else if (ddlImportType.SelectedItem.Text == "Personalizare")
            connectionString = connectionString_XML;
        else
            throw new ApplicationException("Unknown Import Type!");

        Hipocrate.DB.DBConnection.variableConnectionString = connectionString;
        DataSet newDdlItems = Hipocrate.DB.DBConnection.GetDataVariable("ExportItems_Select", new string[] { }, new object[] { });
        Session["ds_export_items"] = newDdlItems;
        newDdlItems.Tables[0].DefaultView.RowFilter = "TypeID='" + ddlImportType.SelectedValue + "'";

        ddl.Items.Clear();
        ddl.DataSource = newDdlItems.Tables[0].DefaultView;
        ddl.DataTextField = "Section_code";
        ddl.DataValueField = "id";
        ddl.AppendDataBoundItems = true;
        ddl.DataBind();

        Load_Grid();
    }

    protected void btnImport_Click(object sender, EventArgs e)
    {
        try
        {
            string strRootPath = System.Web.HttpContext.Current.Request.MapPath("~/Tools/TempNomenclator/");
            var sBuilder = new StringBuilder();

            var Connection = new SqlConnection(connectionString);
            var server = new Server(new ServerConnection(Connection));
            var db = server.Databases[Connection.Database];

            var fileName = strRootPath + drpFiles.SelectedValue;

            if (string.IsNullOrEmpty(fileName))
            {
                message = "Nu ai introdus calea fisierului.";
                this.SetMessage(message, true);
                hideModal();
                return;
            }

            var importTypes = ddlImportType.SelectedItem.Text;
            prefix_Table = ddl.SelectedItem.Text;

            //ImportXML_ADO(connectionString, fileName, importTypes, sBuilder);

            ImportXML(connectionString, fileName, db, sBuilder, importTypes);

            Hipocrate.DB.DBConnection.GetDataVariable("History_Insert", new[] { "UserName", "XML", "tblHistory" }, new object[] { Tools.GetUserName(), prefix_Table, sBuilder.ToString() });

            hideModal();

            Load_Grid();
            this.SetMessage("Importul s-a realizat.", false);
        }
        catch (SmoException ex)
        {
            this.SetMessage(ex.Message, true);
            hideModal();
            return;
        }

        catch (Exception ex)
        {
            this.SetMessage(ex.ToString(), true);
            string js = "hideModal()";
            ScriptManager.RegisterStartupScript(this, typeof(ImportSIUI), "hideModal", js, true);
            return;
        }
    }

    private bool addCustomColumns = true;
    private DataColumn[] GetCustomColumns()
    {
        if (!addCustomColumns) return new DataColumn[0];
        DataColumn[] result = new DataColumn[2];
        DataColumn idColumn = new DataColumn("ID", typeof(long));
        idColumn.AutoIncrement = true;
        result[0] = idColumn;
        DataColumn activeColumn = new DataColumn("IsActive", typeof(Boolean));
        activeColumn.DefaultValue = true;
        result[1] = activeColumn;
        return result;
    }
    private void AddCustomColumns(DataTable table)
    {
        foreach (DataColumn c in GetCustomColumns())
            table.Columns.Add(c);
    }
    private void AddCustomColumns(SmoTable table)
    {
        foreach (DataColumn c in GetCustomColumns())
            table.Columns.Add(new Column(table, c.ColumnName)
                    {
                        DataType = Tools.GetDataType(c.DataType.ToString()),
                        Identity = c.AutoIncrement
                    }
             );
    }

    private void ImportXML_ADO(string connectionString, string fileName, string importTypes, StringBuilder sBuilder)
    {
        var ds = new DataSet();

        var table = Hipocrate.DB.DBConnection.GetDataVar(string.Format("select distinct xsd_path from export_items where typeid = '{0}' and id = {1}", ddlImportType.SelectedValue, ddl.SelectedValue)).Tables[0];
        var xsdFileName = table.Rows != null && table.Rows.Count > 0 ? string.IsNullOrEmpty(table.Rows[0]["xsd_path"].ToString()) ? "" : Server.MapPath(table.Rows[0]["xsd_path"].ToString()) : "";

        if (string.IsNullOrEmpty(xsdFileName))
        {
            message = "Nu este indicata calea catre fisierul xsd in tabelul EXPORT_ITEMS. Verificati.";
            this.SetMessage(message, true);
            hideModal();
            return;
        }

        if (!File.Exists(xsdFileName))
        {
            message = string.Format("Nu exista fisier xsd pe calea - {0}. Verificati.", xsdFileName);
            this.SetMessage(message, true);
            hideModal();
            return;
        }

        try
        {
            var validator = new XMLValidator("");
            if (chkValidation.Checked)
                validator.Validate(fileName, xsdFileName);
            ds.ReadXmlSchema(xsdFileName);
            ds.EnforceConstraints = false;
            ds.ReadXml(fileName, XmlReadMode.ReadSchema);

        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
            return;
        }

        int cntHash = 0;
        Hashtable tablesDB = new Hashtable();
        DataSet ds_tablesDB = Hipocrate.DB.DBConnection.GetDataVar("select * from information_Schema.tables where TABLE_TYPE like '%BASE_TABLE%'");
        foreach (DataRow dr in ds_tablesDB.Tables[0].Rows)
            tablesDB[(cntHash++).ToString()] = dr["TABLE_NAME"];

        Hipocrate.DB.DBConnection.ExecuteVariable("update importedtables set modified = 0");
        for (var i = 0; i < ds.Tables.Count; ++i)
        {
            var tblName = prefix_Table + "_" + ds.Tables[i].TableName;
            //var TestTable = new SmoTable(db, tblName);
            sBuilder.Append(ds.Tables[i].TableName + "-");

            List<string> unknownTypes = new List<string>();
            
            if (unknownTypes.Count > 0)
            {
                string message = "Unknown db types: ";
                foreach (string type in unknownTypes)
                    message += type + ", ";
                throw new ApplicationException(message.TrimEnd(' ', ','));
            }

            var ds2 = Hipocrate.DB.DBConnection.GetDataVariable("tablesImport", new[] { "tableName" }, new object[] { tblName });
        }
    }

    private void ImportXML(string connectionString, string fileName, Database db, StringBuilder sBuilder, string importTypes)
    {
        var ds = new DataSet();

        var table = Hipocrate.DB.DBConnection.GetDataVar(string.Format("select distinct xsd_path from export_items where typeid = '{0}' and id = {1}", ddlImportType.SelectedValue, ddl.SelectedValue)).Tables[0];
        var xsdFileName = table.Rows != null && table.Rows.Count > 0 ? string.IsNullOrEmpty(table.Rows[0]["xsd_path"].ToString()) ? "" : Server.MapPath(table.Rows[0]["xsd_path"].ToString()) : "";

        if (string.IsNullOrEmpty(xsdFileName))
        {
            message = "Nu este indicata calea catre fisierul xsd in tabelul EXPORT_ITEMS. Verificati.";
            this.SetMessage(message, true);
            hideModal();
            return;
        }

        if (!File.Exists(xsdFileName))
        {
            message = string.Format("Nu exista fisier xsd pe calea - {0}. Verificati.", xsdFileName);
            this.SetMessage(message, true);
            hideModal();
            return;
        }

        try
        {
            var validator = new XMLValidator("");
            if (chkValidation.Checked)
                validator.Validate(fileName, xsdFileName);

            ds.ReadXmlSchema(xsdFileName);
            ds.EnforceConstraints = false;
            ds.ReadXml(fileName, XmlReadMode.ReadSchema);


            /* IMPORT FARA SCHEMA XSD*/

            //System.IO.FileStream fsReadXml = new System.IO.FileStream(fileName, System.IO.FileMode.Open);
            //ds.EnforceConstraints = false;
            //ds.ReadXml(fsReadXml);
            //fsReadXml.Close();
        }
        catch (Exception ex)
        {
            this.SetMessage(ex.Message, true);
            return;
        }

        int cntHash = 0;
        Hashtable tablesDB = new Hashtable();
        DataSet ds_tablesDB = Hipocrate.DB.DBConnection.GetDataVar("select * from information_Schema.tables where TABLE_TYPE like '%BASE_TABLE%'");
        foreach (DataRow dr in ds_tablesDB.Tables[0].Rows)
            tablesDB[(cntHash++).ToString()] = dr["TABLE_NAME"];

        Hipocrate.DB.DBConnection.ExecuteVariable("update importedtables set modified = 0");
        for (var i = 0; i < ds.Tables.Count; ++i)
        {

            var tblName = prefix_Table + "_" + ds.Tables[i].TableName;
            var TestTable = new SmoTable(db, tblName);
            sBuilder.Append(ds.Tables[i].TableName + "-");

            List<string> unknownTypes = new List<string>();
            foreach (DataColumn dc in ds.Tables[i].Columns)
                try
                {
                    var tempC = new Column(TestTable, dc.ColumnName)
                    {
                        DataType = Tools.GetDataType(dc.DataType.ToString())
                    };

                    TestTable.Columns.Add(tempC);
                }
                catch (Exception)
                {
                    if (!unknownTypes.Contains(dc.DataType.ToString()))
                        unknownTypes.Add(dc.DataType.ToString());
                }
            if (unknownTypes.Count > 0)
            {
                string message = "Unknown db types: ";
                foreach (string type in unknownTypes)
                    message += type + ", ";
                throw new ApplicationException(message.TrimEnd(' ', ','));
            }

            if (!db.Tables.Contains(tblName))
            {
                AddCustomColumns(TestTable);
                AddCustomColumns(ds.Tables[i]);
                TestTable.Create();
            }

            var ds2 = Hipocrate.DB.DBConnection.GetDataVariable("tablesImport", new[] { "tableName" }, new object[] { tblName });
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var bulkcopy = new SqlBulkCopy(connection))
                {
                    bulkcopy.DestinationTableName = tblName;
                    try
                    {
                        if (ds2.Tables[0].Rows.Count > 0)
                        {
                            var comparedTable = compareDataTables(ds.Tables[i], ds2.Tables[0], Convert.ToString(i));
                            if (ds.Tables[i].Rows.Count.Equals(ds2.Tables[0].Rows.Count))
                            {
                                Hipocrate.DB.DBConnection.ExecuteVariable(string.Format("TRUNCATE TABLE {0}", tblName));
                                bulkcopy.WriteToServer(ds.Tables[i]);
                                Hipocrate.DB.DBConnection.ExecuteVariable("ImportedTables_Update", new object[] { tblName });
                            }
                            else if (!ds.Tables[i].Rows.Count.Equals(ds2.Tables[0].Rows.Count))
                            {
                                Hipocrate.DB.DBConnection.ExecuteVariable(string.Format("TRUNCATE TABLE {0}", tblName));
                                bulkcopy.WriteToServer(ds.Tables[i]);
                                Hipocrate.DB.DBConnection.ExecuteVariable("ImportedTables_Update", new object[] { tblName });
                            }
                        }
                        else
                        {
                            Hipocrate.DB.DBConnection.ExecuteVariable(string.Format("TRUNCATE TABLE {0}", tblName));
                            bulkcopy.WriteToServer(ds.Tables[i]);
                            Hipocrate.DB.DBConnection.ExecuteVariable("ImportedTables_Update", new object[] { tblName });
                        }
                    }
                    catch (Exception ex)
                    {
                        message = ex.Message;
                        this.SetMessage("Cannot import " + tblName + ": " + ex.ToString(), true);
                        hideModal();
                    }

                    connection.Close();
                }
            }
        }
    }

    public DataTable compareDataTables(DataTable First, DataTable Second, string z)
    {
        First.TableName = "FirstTable" + z;
        Second.TableName = "SecondTable" + z;

        DataTable table = new DataTable("Difference");

        try
        {
            using (DataSet ds = new DataSet())
            {
                ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() });

                DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];

                for (int i = 0; i < firstcolumns.Length; i++)
                {
                    firstcolumns[i] = ds.Tables[0].Columns[i];
                }

                DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];

                for (int i = 0; i < secondcolumns.Length; i++)
                {
                    secondcolumns[i] = ds.Tables[1].Columns[i];
                }

                DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
                ds.Relations.Add(r);

                for (int i = 0; i < First.Columns.Count; i++)
                {
                    table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
                }

                table.BeginLoadData();

                foreach (DataRow parentrow in ds.Tables[0].Rows)
                {
                    DataRow[] childrows = parentrow.GetChildRows(r);
                    if (childrows == null || childrows.Length == 0)
                        table.LoadDataRow(parentrow.ItemArray, true);
                }

                table.EndLoadData();

            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

        return table;
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView gvTemp = (GridView)sender;
        gvTemp.PageIndex = e.NewPageIndex;
        gvTemp.DataBind();
    }

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        GridView gvTemp = (GridView)sender;
        gvSortExpr = e.SortExpression;
        DataSet ds = Hipocrate.DB.DBConnection.GetDataVariable("Users_Select2", new string[] { }, new object[] { });
        DataView dv = ds.Tables[0].DefaultView;
        if (gvSortExpr != "")
            dv.Sort = gvSortExpr + " " + GetSortDirection();
        gvTemp.DataSource = dv;
        gvTemp.DataBind();
    }

    protected override void CreateChildControls()
    {
        base.CreateChildControls();
    }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        GridViewRow row = e.Row;
        string strSort = string.Empty;
        string sValueTable = null;
        string sDateTable = null;

        if (e.Row.RowType.ToString() != "Header" && e.Row.RowType.ToString() != "Footer" && e.Row.RowType.ToString() != "Pager")
        {
            sValueTable = ((System.Data.DataRowView)(e.Row.DataItem)).Row.ItemArray[2].ToString();
            sDateTable = ((System.Data.DataRowView)(e.Row.DataItem)).Row.ItemArray[3].ToString();
        }

        if (row.DataItem == null)
        {
            return;
        }

        GridView gv = new GridView();
        gv = (GridView)row.FindControl("GridView2");
        DataSet ds = Hipocrate.DB.DBConnection.GetDataVariable("History_Tables", new[] { "XML", "DateTable" }, new object[] { sValueTable, sDateTable });
        DataView dv = ds.Tables[0].DefaultView;
        gv.DataSource = dv;
        gv.DataBind();
    }

    protected void hideModal()
    {
        string js = "hideModal()";
        ScriptManager.RegisterStartupScript(this, typeof(ImportSIUI), "hideModal", js, true);
    }
}
